package com.bw.mapper;

import com.bw.bean.Vo1;
import com.bw.bean.Vo2;
import com.bw.bean.Vo3;
import com.bw.bean.Vo4;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface VoMapper {

    @Select("select s.name,sum(oi.original_amount)'value',count(oi.id)num1,count(distinct oi.customer_id)num2,\n" +
            "       sum(oi.actual_amount)num3, sum(p.amount)num4 from order_info oi\n" +
            "left join shop s on oi.shop_id = s.id\n" +
            "left join payment p on s.id = p.shop_id\n" +
            "where p.status = 5\n" +
            "group by s.name;")
    List<Vo1> getList1();

    @Select("select oi.create_day,1 value,sum(if(s.type=1,p.reduce_amount,0))num1,\n" +
            "       sum(if(s.type=1,p.reduce_amount,0))num2,\n" +
            "       sum(if(p.company_share,p.reduce_amount,0))num3 from order_info oi\n" +
            "left join promotion p on oi.promotion_id = p.id\n" +
            "left join shop s on oi.shop_id = s.id\n" +
            "group by oi.create_day,1;")
    List<Vo2> getList2();

    @Select("select p.name,avg(if(oi.rating = 5,1,0))/count(1)'value' from order_info oi\n" +
            "left join order_detail od on oi.id = od.order_info_id\n" +
            "left join product_sku ps on od.product_sku_id = ps.id\n" +
            "left join product_spu p on ps.product_spu_id = p.id\n" +
            "group by p.name;")
    List<Vo3> getList3();

    @Select("select p.name,avg(oi.rating)'value' from order_info oi\n" +
            "left join order_detail od on oi.id = od.order_info_id\n" +
            "left join product_sku ps on od.product_sku_id = ps.id\n" +
            "left join product_spu p on ps.product_spu_id = p.id\n" +
            "group by p.name;")
    List<Vo3> getList4();

    @Select("select hour(create_time)'name',count(customer_id)'value' from order_info group by name;")
    List<Vo4> getList5();
}
